/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bionic.ce.daoimplementation;

import com.bionic.ce.beans.Currency;
import com.bionic.ce.beans.Systemacc;
import com.bionic.ce.beans.User;
import com.bionic.ce.dao.ISystemaccDAO;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MySQLSystemaccDAO extends MYSQLDAO implements ISystemaccDAO {

    @Override
    public void add(Systemacc systemacc) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "INSERT INTO systemacc "
                    + "(`Currency`, `User_id`) "
                    + "VALUES(?,?)";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);

            String shortname = systemacc.getCurrency();
            Currency currency = new Currency();
            currency.setShortName(shortname);
            boolean b = new MySQLCurrencyDAO().findByDynamicSelect(currency).isEmpty();
            if (!b) {
                ptmt.setString(1, systemacc.getCurrency());
            } else {
                throw new SQLException("Such currency with shortname=" + systemacc.getCurrency() + " doesnt exists");
            }

            int id = systemacc.getUserId();
            User user = new User();
            user.setUserid(id);
            b = new MySQLUserDAO().findByDynamicSelect(user).isEmpty();
            if (!b) {
                ptmt.setInt(2, systemacc.getUserId());
            } else {
                throw new SQLException("Such User with id=" + systemacc.getUserId() + " doesnt exists");
            }

            ptmt.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void update(Systemacc systemacc) throws SQLException {
        connection = getConnection();

        try {
            String queryString = "UPDATE systemacc SET `Amount`=?, `Status`=? "
                    + "WHERE `AccNum`=?";

            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setDouble(1, systemacc.getAmount());
            ptmt.setString(2, systemacc.getStatus());
            ptmt.setInt(3, systemacc.getSystemaccId());
            ptmt.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void deleteByAccNum(int AccNum) throws SQLException {
        connection = getConnection();
        try {
            String queryString = "DELETE FROM systemacc WHERE `AccNum`=?";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            ptmt.setInt(1, AccNum);
            ptmt.executeUpdate();
            connection.commit();
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<Systemacc> findAll() throws SQLException {
        connection = getConnection();
        try {

            List<Systemacc> result = new ArrayList();
            String queryString = "SELECT * FROM systemacc";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                Systemacc systemacc = new Systemacc();
                systemacc.setSystemaccId(resultSet.getInt("AccNum"));
                systemacc.setAmount(resultSet.getDouble("Amount"));
                systemacc.setStatus(resultSet.getString("Status"));
                systemacc.setCurrency(resultSet.getString("Currency"));
                systemacc.setUserId(resultSet.getInt("User_id"));
                result.add(systemacc);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public List<Systemacc> findByDynamicSelect(Systemacc systemacc) throws SQLException {
        connection = getConnection();
        try {
            List<Systemacc> result = new ArrayList();
            String queryString = "SELECT * FROM systemacc WHERE ";
            int i = 0;
            if (systemacc.getSystemaccId() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`AccNum`=" + systemacc.getSystemaccId();
                i++;
            }
            if (systemacc.getAmount() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Amount`=" + systemacc.getAmount();
                i++;
            }
            if (systemacc.getStatus() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Status`=\"" + systemacc.getStatus() + "\"";
                i++;
            }
            if (systemacc.getCurrency() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`Currency`=\"" + systemacc.getCurrency() + "\"";
                i++;
            }
            if (systemacc.getUserId() != null) {
                if (i > 0) {
                    queryString += " and ";
                }
                queryString += "`User_id`=" + systemacc.getUserId();
                i++;
            }
            queryString += ";";
            connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            connection.setAutoCommit(false);
            ptmt = connection.prepareStatement(queryString);
            resultSet = ptmt.executeQuery();
            while (resultSet.next()) {
                systemacc = new Systemacc();
                systemacc.setSystemaccId(resultSet.getInt("AccNum"));
                systemacc.setAmount(resultSet.getDouble("Amount"));
                systemacc.setStatus(resultSet.getString("Status"));
                systemacc.setCurrency(resultSet.getString("Currency"));
                systemacc.setUserId(resultSet.getInt("User_id"));
                result.add(systemacc);
            }
            connection.commit();
            return result;
        } catch (SQLException e) {
            rollbackQuietly(connection);
            throw e;
        } finally {
            closeQuietly(connection);
            closeQuietly(ptmt);
        }
    }

    @Override
    public void executedQueryMoneyTransfer(Integer userFromId, String currencyFrom, String currencyTo, Integer userToId, Double queryAmount, Double exchangedAmount) throws SQLException {
        connection = getConnection();
        Systemacc systemacc = new Systemacc();

        systemacc.setUserId(userFromId);
        systemacc.setCurrency(currencyFrom);
        Systemacc userFromAccountFrom = (Systemacc) findByDynamicSelect(systemacc).toArray()[0];

        systemacc.setCurrency(currencyTo);
        Systemacc userFromAccountTo = (Systemacc) findByDynamicSelect(systemacc).toArray()[0];

        systemacc.setUserId(userToId);
        systemacc.setCurrency(currencyFrom);
        Systemacc userToAccountFrom = (Systemacc) findByDynamicSelect(systemacc).toArray()[0];

        systemacc.setCurrency(currencyTo);
        Systemacc userToAccountTo = (Systemacc) findByDynamicSelect(systemacc).toArray()[0];

        connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        connection.setAutoCommit(false);

        userFromAccountFrom.setAmount(roundResult(userFromAccountFrom.getAmount() - queryAmount, 2));
        userFromAccountTo.setAmount(userFromAccountTo.getAmount() + exchangedAmount);
        userToAccountFrom.setAmount(userToAccountFrom.getAmount() + queryAmount);
        userToAccountTo.setAmount(roundResult(userToAccountTo.getAmount() - exchangedAmount, 2));
        update(userFromAccountFrom);
        update(userFromAccountTo);
        update(userToAccountFrom);
        update(userToAccountTo);
        connection.commit();
    }

    private static double roundResult(double d, int precise) {
        precise = 10 ^ precise;
        d = d * precise;
        int i = (int) Math.round(d);
        return (double) i / precise;

    }
}
